package cz.muni.fi.pb138.avradiation.web;

import cz.muni.fi.pb138.avradiation.DB.DBUtils;
import cz.muni.fi.pb138.avradiation.Sensor.Sensor;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author huzvak
 */
public class SensorManagerImpl {
    
    private static final Logger logger = Logger.getLogger(
         SensorManagerImpl.class.getName());
    
    private DataSource ds;

    /** 
     * Set DataSource to acces to DB
     * @param DataSource ds  
     * @throws IllegalArgumentException     
     */
    public void setDataSource(DataSource ds) {
        if(ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalArgumentException("DataSource is null");
        }
        this.ds = ds;
    }
    
    /** 
     * Check if DataSource is not null
     * @param DataSource ds  
     * @throws IllegalStateException     
     */
    private void checkDataSource() {
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null");
        }
    }
    
    public List<Sensor> getSensorsByIdCollection(Collection<String> id) {
        if (id == null) {
            throw new IllegalArgumentException("Argument 'id' is null");
        }
        
        List<Sensor> sensors = new ArrayList<Sensor>();
        for (String sensorID : id) {
            sensors.add(getSensorWithMeasuredValuesById(Long.valueOf(sensorID)));
        }
        
        return sensors;
    }
    
    public List<Sensor> getSensorsByIdAndDateCollection(Collection<String> id, String startTime, String endTime) {
        if (id == null) {
            throw new IllegalArgumentException("Argument 'id' is null");
        }
        
        List<Sensor> sensors = new ArrayList<Sensor>();
        for (String sensorID : id) {
            sensors.add(getMeasuredValuesByTimeAndSensor(Long.valueOf(sensorID), startTime, endTime));
        }
        
        return sensors;
    }
    
    public Collection<String> getMeasureDays() {
        checkDataSource();

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT DATE_FORMAT((FROM_UNIXTIME(mtime/1000) + 0),\"%Y-%m-%d\") "
                    + "as mdate FROM measured_value GROUP BY mdate ORDER BY mdate");

            ResultSet rs = st.executeQuery();

            List<String> mdate = new ArrayList<String>();
            while (rs.next()) {
                mdate.add(rs.getString("mdate"));
            }
            return mdate;

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting measure times from DB", ex);
            throw new RuntimeException("Error when getting measure times from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }                
    }
    
    public Collection<Sensor> getSensorsByLocation(Long locationId) {
        
        if (locationId == null) {
            logger.log(Level.SEVERE,"Parameter [locationId] is null");
            throw new IllegalArgumentException("Parameter [locationId] is null");
        }
        checkDataSource();

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT * FROM sensor WHERE idlocation = ? ORDER BY name");
            st.setLong(1, locationId);

            ResultSet rs = st.executeQuery();

            List<Sensor> sensors = new ArrayList<Sensor>();
            while (rs.next()) {
                Sensor result = new Sensor();
                result.setId(rs.getLong("idsensor"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));               
                
                sensors.add(result);
            }
            logger.log(Level.INFO,"Sensors succesfully selected");
            return sensors;

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting sensors from DB", ex);
            throw new RuntimeException("Error when getting sensors from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }                
    }

    public Sensor getInfoAboutSensorById(Long id) {
        
        if (id == null) {
            logger.log(Level.SEVERE,"Parameter [id] is null");
            throw new IllegalArgumentException("Parameter [id] is null");
        }
        
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null.");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT s.idsensor AS id, s.name AS name, s.unit AS unit, s.description AS description, "
                    + "l.name AS location "
                    + "FROM sensor s JOIN location l ON s.idlocation=l.idlocation WHERE s.idsensor = ?");
            st.setLong(1, id);

            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                Sensor result = new Sensor();

                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));
                result.setName(rs.getString("location"));
            
                assert !rs.next();
                logger.log(Level.INFO,"Sensor found", result);
                return result;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting sensor from DB", ex);
            throw new RuntimeException("Error when getting sensor from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }                
    }

    public Sensor getSensorWithMeasuredValuesById(Long id) {
        if (id == null) {
            logger.log(Level.SEVERE,"Parameter [id] is null");
            throw new IllegalArgumentException("Parameter [id] is null");
        }
        
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null.");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT s.idsensor AS id, s.name AS name, s.unit AS unit, s.description AS description, "
                    + "l.name AS location "
                    + "FROM sensor s JOIN location l ON s.idlocation=l.idlocation WHERE s.idsensor = ?");
            st.setLong(1, id);

            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                Sensor result = new Sensor();

                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));
                result.setLocation(rs.getString("location"));
                
                PreparedStatement st2 = conn.prepareStatement(
                    "SELECT mval, mtime FROM measured_value WHERE idsensor = ?");
                st2.setLong(1, result.getId());

                ResultSet rs2 = st2.executeQuery();                
                
                while (rs2.next()) {                    
                                        
                    result.addMeasuredValue(rs2.getLong("mtime"), rs2.getBigDecimal("mval"));
                }                
            
                assert !rs.next();
                logger.log(Level.INFO,"Sensor found", result);
                return result;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting sensor from DB", ex);
            throw new RuntimeException("Error when getting sensor from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    public Collection<Sensor> getInfoAboutAllSensors() {
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null.");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement("SELECT s.idsensor AS id, s.name AS name, s.unit AS unit, s.description AS description, "
                    + "l.name AS location "
                    + "FROM sensor s JOIN location l ON s.idlocation=l.idlocation ORDER BY s.name");
            ResultSet rs = st.executeQuery();
            Collection<Sensor> sensors = new LinkedList<Sensor>();
            while (rs.next()) {
                Sensor result = new Sensor();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));
                result.setLocation(rs.getString("location"));                
                
                sensors.add(result);
            }
            logger.log(Level.INFO,"Sensors succesfully selected");
            return Collections.unmodifiableCollection(sensors);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting all sensors from DB", ex);
            throw new RuntimeException("Error when getting all sensors from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    public Collection<Sensor> getAllSensorsWithMeasuredValues() {
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null.");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement("SELECT s.idsensor AS id, s.name AS name, s.unit AS unit, s.description AS description, "
                    + "l.name AS location "
                    + "FROM sensor s JOIN location l ON s.idlocation=l.idlocation ORDER BY s.name");
            ResultSet rs = st.executeQuery();
            Collection<Sensor> sensors = new LinkedList<Sensor>();
            while (rs.next()) {
                Sensor result = new Sensor();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));
                result.setLocation(rs.getString("location"));
                
                PreparedStatement st2 = conn.prepareStatement(
                    "SELECT mval, mtime FROM measured_value WHERE idsensor = ? ORDER BY mtime");
                st2.setLong(1, result.getId());                

                ResultSet rs2 = st2.executeQuery();                
                
                while (rs.next()) {                    
                                        
                    result.addMeasuredValue(rs2.getLong("mtime"), rs2.getBigDecimal("mval"));
                }
                
                sensors.add(result);
            }
            logger.log(Level.INFO,"Sensors succesfully selected");
            return Collections.unmodifiableCollection(sensors);
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting all sensors from DB", ex);
            throw new RuntimeException("Error when getting all sensors from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    public Sensor getMeasuredValuesByTimeAndSensor(Long idSensor, String startTime, String endTime) {
        
        if (idSensor == null) {
            logger.log(Level.SEVERE,"Parameter [idSensor] is null");
            throw new IllegalArgumentException("Parameter [idSensor] is null");
        }
        
        if (startTime == null) {
            logger.log(Level.SEVERE,"Parameter [startTime] is null");
            throw new IllegalArgumentException("Parameter [startTime] is null");
        }
        
        if (endTime == null) {
            logger.log(Level.SEVERE,"Parameter [endTime] is null");
            throw new IllegalArgumentException("Parameter [endTime] is null");
        }
        
        if (ds == null) {
            logger.log(Level.SEVERE,"DataSource is null");
            throw new IllegalStateException("DataSource is null.");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT s.idsensor AS id, s.name AS name, s.unit AS unit, s.description AS description, "
                    + "l.name AS location "
                    + "FROM sensor s JOIN location l ON s.idlocation=l.idlocation WHERE s.idsensor = ?");
            st.setLong(1, idSensor);

            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                Sensor result = new Sensor();

                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setUnit(rs.getString("unit"));
                result.setDescription(rs.getString("description"));
                result.setLocation(rs.getString("location"));
                
                PreparedStatement st2 = conn.prepareStatement(
                    "SELECT mval, mtime FROM measured_value WHERE idsensor = ? AND "
                        + "mtime BETWEEN (unix_timestamp(?)*1000) AND ((unix_timestamp(?)+24*60*60-1)*1000)");
                st2.setLong(1, result.getId());
                st2.setString(2, startTime);
                st2.setString(3, endTime);

                ResultSet rs2 = st2.executeQuery();                
                
                while (rs2.next()) {                    
                                        
                    result.addMeasuredValue(rs2.getLong("mtime"), rs2.getBigDecimal("mval"));
                }                
            
                assert !rs.next();
                logger.log(Level.INFO,"Sensor found", result);
                return result;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            logger.log(Level.SEVERE, "Error when getting sensor from DB", ex);
            throw new RuntimeException("Error when getting sensor from DB", ex);
        } finally {
            DBUtils.closeConnection(conn);
        }
    }
    
    
    public void storeSensors(Collection<Sensor> sensors) {
        if (sensors == null) {
            throw new IllegalArgumentException("Sensors is null.");
        }

        Iterator<Sensor> sensorsIterator = sensors.iterator();
        Sensor sensor = null;
        while (sensorsIterator.hasNext()) {
            sensor = sensorsIterator.next();
            getSensorId(sensor);
            if (sensor.getId() == null || sensor.getId() == -1) {
                continue;
            }
            Iterator<Long> valuesIterator = sensor.getMeasuredValues().keySet().iterator();
            Long time = null;
            BigDecimal value = null;
            while (valuesIterator.hasNext()) {
                time = valuesIterator.next();
                value = sensor.getMeasuredValues().get(time);
                saveMeasuredValue(sensor.getId(), time, value);
            }
        }

    }

    /**
     * Save value into database
     *
     * @param id Id sensor, on which was value measured
     * @param time Time when was value measured
     * @param value Value
     */
    private void saveMeasuredValue(Long id, Long time, BigDecimal value) {
        checkDataSource();
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into measured_value(mval, mtime, idsensor) values(?, ?, ?)");
            if(value == null){
                ps.setNull(1, Types.DECIMAL);
            }else{
                ps.setBigDecimal(1, value);
            }
            ps.setLong(2, time);
            ps.setLong(3, id);
            int count = ps.executeUpdate();
            if(count != 1){
                throw new SQLException("Error when inserting in DB");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    /**
     * Get sensor ID for given sensor
     * @param sensor Sensor
     */
    private void getSensorId(Sensor sensor) {
        checkDataSource();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        long locationId = getLocationId(sensor.getLocation());

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("select idsensor from sensor where idlocation=? and name like ?");
            ps.setLong(1, locationId);
            ps.setString(2, sensor.getName());
            rs = ps.executeQuery();
            if (rs.next()) {
                sensor.setId(rs.getLong("idsensor"));
            } else {
                long sensorId = createSensor(sensor, locationId);
                if (sensorId != -1) {
                    sensor.setId(sensorId);
                } else {
                    throw new IllegalArgumentException("Sensor ID is -1");
                }
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting sensor ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    /**
     * Creates sensor in database
     *
     * @param sensor Sensor
     * @param locationId Location ID
     * @return New sensor ID
     */
    private long createSensor(Sensor sensor, long locationId) {
        checkDataSource();
        if (sensor == null || locationId == -1) {
            return -1;
        }
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into sensor(name, unit, idlocation) values(?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, sensor.getName());
            ps.setString(2, sensor.getUnit());
            ps.setLong(3, locationId);
            ps.executeUpdate();
            long sensorId = DBUtils.getId(ps.getGeneratedKeys());
            if (sensorId != -1) {
                sensor.setId(sensorId);
                return sensorId;
            } else {
                throw new IllegalArgumentException("Sensor ID is -1.");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }

    /**
     * Get location ID for given location
     *
     * @param location Location
     * @return Location ID
     */
    private long getLocationId(String location) {
        checkDataSource();
        if (location == null) {
            throw new IllegalArgumentException("Location is null");
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("select idlocation from location where name like ?");
            ps.setString(1, location);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong("idlocation");
            } else {
                return createLocation(location);
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }

    /**
     * Creates location in database
     *
     * @param location Location
     * @return New location ID
     */
    private long createLocation(String location) {
        checkDataSource();
        if (location == null) {
            throw new IllegalArgumentException("Location is null");
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into location(name) values(?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, location);
            ps.executeUpdate();
            long locationId = DBUtils.getId(ps.getGeneratedKeys());
            if (locationId != -1) {
                return locationId;
            } else {
                throw new IllegalArgumentException("Location ID is -1");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }
    
}
